﻿Imports System.Data.SqlClient
Imports DTOLayer
Public Class XeDao
    Public Function LayDanhSach() As List(Of XeDto)
        Dim ds As New List(Of XeDto)
        Dim con As SqlConnection
        Dim strSQL As String
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        con = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        strSQL = "Select * From Xe"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, con)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        Dim xe As New XeDto()
        While (dr.Read())
            xe = New XeDto()
            xe.IdXe = dr("idXe")
            xe.BienSo = dr("BienSo")
            xe.TenXe = dr("TenXe")
            xe.HieuXe = dr("HieuXe")
            xe.IdKH = dr("idKH")
            ds.Add(xe)
        End While
        'B5: Dong ket noi CSDL
        con.Close()
        Return ds
    End Function

    Public Function TraCuuBienSo(ByVal bienSo As String) As XeDto
        Dim xe As New XeDto
        Dim cn As SqlConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From Xe Where BienSo = ?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@BienSo", SqlDbType.VarChar)
        cmd.Parameters("@BienSo").Value = bienSo
        'B4: Thuc thi chuoi strSQL
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        While dr.Read()
            xe = New XeDto()
            xe.IdXe = dr("idXe")
            xe.BienSo = dr("BienSo")
            xe.TenXe = dr("TenXe")
            xe.HieuXe = dr("HieuXe")
            xe.IdKH = dr("idKH")
        End While
        'B5: Dong ket noi CSDL
        cn.Close()
        Return xe
    End Function

    Public Sub Them(ByVal xeDto As XeDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Insert into Xe(BienSo,TenXe,HieuXe,idKH) values(?,?,?,?)"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@BienSo", SqlDbType.VarChar)
        cmd.Parameters("@BienSo").Value = xeDto.BienSo
        cmd.Parameters.Add("@TenXe", SqlDbType.VarChar)
        cmd.Parameters("@TenXe").Value = xeDto.TenXe
        cmd.Parameters.Add("@HieuXe", SqlDbType.VarChar)
        cmd.Parameters("@HieuXe").Value = xeDto.HieuXe
        cmd.Parameters.Add("@idKH", SqlDbType.Int)
        cmd.Parameters("@idKH").Value = xeDto.IdKH
        cmd.ExecuteNonQuery()
        strSQL = "Select @@IDENTITY"
        cmd = New SqlCommand(strSQL, cn)
        xeDto.IdXe = cmd.ExecuteScalar()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Sua(ByVal xeDto As XeDto)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Update Xe set BienSo=?, TenXe=?, HieuXe=?, idKH=? where idXe=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@BienSo", SqlDbType.VarChar)
        cmd.Parameters("@BienSo").Value =
        cmd.Parameters.Add("@TenXe", SqlDbType.VarChar)
        cmd.Parameters("@TenXe").Value = XeDto.TenXe
        cmd.Parameters.Add("@HieuXe", SqlDbType.VarChar)
        cmd.Parameters("@HieuXe").Value = XeDto.HieuXe
        cmd.Parameters.Add("@idKH", SqlDbType.Int)
        cmd.Parameters("@idKH").Value = xeDto.IdKH
        cmd.Parameters.Add("@idXe", SqlDbType.Int)
        cmd.Parameters("@idXe").Value = xeDto.IdKH                
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub Xoa(ByVal idKh As Integer)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "delete ThongTinKhachHang where idKH=?"
        'B4: Thuc thi chuoi strSQL
        Dim cmd As New SqlCommand(strSQL, cn)
        cmd.Parameters.Add("@idKH", SqlDbType.Int)
        cmd.Parameters("@idKH").Value = idKh
        cmd.ExecuteNonQuery()
        cmd = New SqlCommand(strSQL, cn)
        cmd.ExecuteNonQuery()
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Sub CapNhatBang(ByVal dt As DataTable)
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From Xe"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        Dim cb As New SqlCommandBuilder(da)
        da.Update(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
    End Sub

    Public Function LayBang() As DataTable
        Dim dt As New DataTable()
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        Dim cn As SqlConnection
        cn = DataProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim strSQL As String
        strSQL = "Select * From Xe"
        'B4: Thuc thi chuoi strSQL
        Dim da As New SqlDataAdapter(strSQL, cn)
        da.Fill(dt)
        'B5: Dong ket noi CSDL
        cn.Close()
        Return dt
    End Function
End Class
